﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CLibArtreve.dal
{
    public static class Queries
    {
        #region ART QUERIES
            #region SELECT
        public const string SELECTALLARTS = "SELECT * FROM Arts WHERE art_deleteddate IS NULL ORDER BY art_creationdate DESC";        
        public const string SELECTALLARTSWITHCATEGORIES = @"
                SELECT arts.*, Categories.category_name, users.user_nickname
                FROM Arts                                 
                LEFT OUTER JOIN users ON users.user_id = arts.users_user_id                
                LEFT OUTER JOIN ArtCategories on ArtCategories.arts_art_id = Arts.art_id
                INNER JOIN Categories on category_id = ArtCategories.categories_category_id                
                WHERE art_deleteddate IS NULL
                ORDER BY art_creationdate DESC";
            public const string SELECTALLSPOTLIGHTEDARTS = "SELECT * FROM Arts WHERE art_spotlighted = @art_spotlighted AND art_deleteddate IS NULL ORDER BY art_creationdate DESC";
            public const string SELECTARTBYID = @"
                SELECT arts.*, users.user_id, users.user_nickname, ratings.rating_stars, Categories.category_name
                FROM arts 
                LEFT OUTER JOIN users ON users.user_id = arts.users_user_id
                LEFT OUTER JOIN ratings ON ratings.users_user_id = arts.users_user_id
                LEFT OUTER JOIN ArtCategories on ArtCategories.arts_art_id = Arts.art_id
                LEFT OUTER JOIN Categories on category_id = ArtCategories.categories_category_id
                WHERE art_id = @art_id AND art_deleteddate IS NULL";
            /*public const string SELECTARTANDDELETEDARTBYID = @"
                SELECT arts.*, users.user_id, users.user_nickname, ratings.rating_stars, Categories.category_name
                FROM arts 
                LEFT OUTER JOIN users ON users.user_id = arts.users_user_id
                LEFT OUTER JOIN ratings ON ratings.users_user_id = arts.users_user_id
                LEFT OUTER JOIN ArtCategories on ArtCategories.arts_art_id = Arts.art_id
                LEFT OUTER JOIN Categories on category_id = ArtCategories.categories_category_id";*/
            public const string SELECTALLARTBYUSERID = @"
                SELECT arts.*, Categories.category_name
                FROM arts
                INNER JOIN ArtCategories on ArtCategories.arts_art_id = Arts.art_id
                INNER JOIN Categories on category_id = ArtCategories.categories_category_id
                WHERE users_user_id = @user_id AND art_deleteddate IS NULL
                ORDER BY art_creationdate DESC";
            public const string SELECTALLDELETEDARTBYUSERID = @"
                SELECT arts.*, Categories.category_name
                FROM arts
                INNER JOIN ArtCategories on ArtCategories.arts_art_id = Arts.art_id
                INNER JOIN Categories on category_id = ArtCategories.categories_category_id
                WHERE users_user_id = @user_id AND art_deleteddate IS NOT NULL
                ORDER BY art_creationdate DESC";    
            public const string SELECTARTSBYCATEGORYID = @"
                SELECT arts.*, users.user_nickname
                FROM arts                
                INNER JOIN users ON users.user_id = arts.users_user_id
                INNER JOIN ArtCategories ON ArtCategories.arts_art_id = Arts.art_id
                WHERE categories_category_id = @categories_category_id AND art_deleteddate IS NULL";
            public const string SELECTARTSFORARTBOARDSBYUSERID = @"
                SELECT Arts.* 
                FROM UserArts
                INNER JOIN Arts ON UserArts.art_id = Arts.art_id
                WHERE UserArts.user_id = @user_id AND Arts.art_deleteddate IS NULL";
            #endregion

            #region INSERT
            public const string INSERTART = @"INSERT 
                        INTO Arts
                        (art_name, art_description, art_size, art_price, art_numberof, art_creationdate, art_spotlighted, artpictures_artpicture_id, users_user_id)
                        VALUES
                        (@art_name, @art_description, @art_size, @art_price, @art_numberof, @art_creationdate, @art_spotlighted, @artpictures_artpicture_id, @users_user_id)";
            #endregion

            #region UPDATE
            public const string UPDATEART = @"UPDATE 
                        Arts
                        SET art_name=@art_name, art_description=@art_description, art_size=@art_size, art_price=@art_price, art_numberof=@art_numberof, art_creationdate=@art_creationdate, art_spotlighted=@art_spotlighted, artpictures_artpicture_id=@artpictures_artpicture_id
                        WHERE art_id=@art_id";
            public const string UPDATEARTDELETEDATE = @"UPDATE 
                        Arts
                        SET art_deleteddate=@art_deleteddate
                        WHERE art_id=@art_id";
            #endregion
        #endregion

        #region ARTPICTURE QUERIES
            #region SELECT
            public const string SELECTALLARTPICTURES = "SELECT * FROM ArtPictures";
            public const string SELECTARTPICTUREBYPICTUREID = "SELECT * FROM ArtPictures WHERE artpicture_id = @artpicture_id";
            public const string SELECTARTPICTUREBYARTID = "SELECT * FROM ArtPictures WHERE arts_art_id = @arts_art_id";
            #endregion

            #region INSERT
            public const string INSERTARTPICTURE = @"INSERT 
                        INTO ArtPictures
                        (artpicture_image, arts_art_id)
                        VALUES
                        (@artpicture_image, @arts_art_id)";
            #endregion

            #region UPDATE
            public const string UPDATEARTPICTURE = @"UPDATE 
                        ArtPictures
                        SET artpicture_image=@artpicture_image
                        WHERE arts_art_id=@arts_art_id";
            #endregion

            #region DELETE
            public const string DELETERARTPICTURE = @"DELETE 
                        FROM ArtPictures
                        WHERE arts_art_id=@arts_art_id";
            #endregion
        #endregion

        #region ARTCATEGORY QUERIES
            #region SELECT
            public const string SELECTALLARTCATEGORIES = "SELECT * FROM ArtCategories";
            public const string SELECTALLCATEGORIESBYARTID = "SELECT * FROM ArtCategories WHERE arts_art_id = @arts_art_id";
            public const string SELECTALLARTSBYCATEGORYID = "SELECT * FROM ArtCategories WHERE categories_category_id = @categories_category_id";
            #endregion

            #region INSERT
            public const string INSERTARTCATEGORY = @"INSERT 
                        INTO ArtCategories
                        (arts_art_id, categories_category_id)
                        VALUES
                        (@arts_art_id, @categories_category_id)";
            #endregion

            #region UPDATE
            public const string UPDATEARTCATEGORY = @"UPDATE 
                        ArtCategories
                        SET categories_category_id=@categories_category_id
                        WHERE arts_art_id=@arts_art_id";
            #endregion


        #endregion

        #region CATEGORY QUERIES

            #region SELECT
            public const string SELECTALLCATEGORIES = "SELECT * FROM Categories";
            public const string SELECTCATEGORYBYID = "SELECT * FROM Categories WHERE category_id = @category_id";
            public const string SELECTALLCATEGORIESBYUSERID = @"
                SELECT DISTINCT Categories.category_name
                FROM Arts
                INNER JOIN ArtCategories on ArtCategories.arts_art_id = Arts.art_id
                INNER JOIN Categories on category_id = ArtCategories.categories_category_id
                WHERE users_user_id = @user_id";
            //DIT MOET NOG CATEGORIES WORDEN?
            public const string SELECTCATEGORYBYARTID = @"
                SELECT categories.* 
                FROM ArtCategories
                INNER JOIN categories ON ArtCategories.categories_category_id = categories.category_id
                WHERE arts_art_id = @arts_art_id";
            #endregion

            #region INSERT
            public const string INSERTCATEGORY = @"INSERT 
                        INTO Categories
                        (category_name, category_description, category_imgUrl)
                        VALUES
                        (@category_name, @category_description, @category_imgUrl)";
            #endregion

        #endregion

        #region COUNTRIES QUERIES
            #region SELECT
            public const string SELECTALLCOUNTRIES = "SELECT * FROM Countries";
            public const string SELECTCOUNTRYBYID = "SELECT * FROM Countries WHERE country_id = @country_id";
            #endregion
        #endregion

        #region RATINGS QUERIES
            #region SELECT
            public const string SELECTALLRATINGS = "SELECT * FROM Ratings";
            public const string SELECTRATINGBYID = "SELECT * FROM Ratings WHERE rating_art_id = @rating_art_id";
            public const string SELECTRATINGBYARTIDANDUSERID = "SELECT * FROM Ratings WHERE users_user_id = @users_user_id AND rating_art_id = @rating_art_id ";
            public const string SELECTRATINGBYUSERID = "SELECT * FROM Ratings WHERE users_user_id = @users_user_id";

            #endregion

            #region INSERT
            public const string INSERTRATING = @"INSERT 
                        INTO Ratings
                        (rating_art_id, rating_stars, users_user_id)
                        VALUES
                        (@rating_art_id, @rating_stars, @users_user_id)";
            #endregion

            #region UPDATE
            public const string UPDATERATING = @"UPDATE 
                        Ratings
                        SET rating_stars=@rating_stars
                        WHERE rating_art_id=@rating_art_id AND users_user_id=@users_user_id";
            #endregion
        #endregion

        #region USERS QUERIES
            #region SELECT
            public const string SELECTALLUSERS = "SELECT * FROM Users";
            public const string SELECTUSERBYID = "SELECT * FROM Users WHERE user_id = @user_id";
            public const string SELECTUSERSBYEMAIL = "SELECT * FROM Users WHERE user_email = @user_email";
            #endregion

            #region INSERT
            public const string INSERTUSER = @"INSERT 
                    INTO users
                    (user_nickname,user_firstname,user_givenname,user_email,user_password,user_dob,user_secretquestion_id,
                        user_secretanswer,user_activationdate,user_creationdate,user_lockeddate)
                    VALUES
                    (@user_nickname,@user_firstname,@user_givenname,@user_email,@user_password,@user_dob,@user_secretquestion_id,
                        @user_secretanswer,@user_activationdate,@user_creationdate,@user_lockeddate)";
            #endregion

            #region UPDATE
            public const string UPDATEUSERWITHOUTPASSWORD = @"UPDATE Users SET 
                            user_nickname=@user_nickname,
                            user_firstname=@user_firstname,
                            user_givenname=@user_givenname,
                            user_email=@user_email,
                            user_dob=@user_dob,
                            user_modifieddate=@user_modifieddate,
                            user_secretquestion_id=@user_secretquestion_id,
                            user_secretanswer=@user_secretanswer
                        WHERE user_id=@user_id";
            public const string UPDATEUSERWITHPASSWORD = @"UPDATE Users SET 
                            user_nickname=@user_nickname,
                            user_firstname=@user_firstname,
                            user_givenname=@user_givenname,
                            user_email=@user_email,
                            user_password=@user_password,
                            user_dob=@user_dob,
                            user_modifieddate=@user_modifieddate,
                            user_secretquestion_id=@user_secretquestion_id,
                            user_secretanswer=@user_secretanswer
                        WHERE user_id=@user_id";
            public const string UPDATEUSERTOKENS = @"UPDATE Users SET 
                            user_tokens=@user_tokens
                        WHERE user_id=@user_id";
            #endregion
        #endregion

        #region USERART QUERIES
            #region SELECT
            public const string SELECTALLUSERART = "SELECT * FROM UserArts";
            public const string SELECTALLARTSBYUSERID = "SELECT * FROM UserArts WHERE UserArts.user_id = @user_id";
            public const string SELECTALLUSERSBYARTID = "SELECT * FROM UserArts WHERE art_id = @art_id";
            #endregion

            #region INSERT
            public const string INSERTUSERART = @"INSERT 
                        INTO UserArts
                        (user_id, art_id)
                        VALUES
                        (@user_id, @art_id)";
            #endregion
 
            #region DELETE
            public const string DELETEUSERART = @"DELETE 
                        FROM UserArts
                        WHERE art_id=@art_id
                        AND user_id=@user_id";
            #endregion
        #endregion

        #region USERADRESS QUERIES
                #region SELECT
                public const string SELECTALLUSERADRESSES = "SELECT * FROM UsersAdresses";
                public const string SELECTALLUSERSBYADRESSID = "SELECT * FROM UsersAdresses WHERE address_id = @address_id";
                public const string SELECTALLADRESSESBYUSERID = "SELECT * FROM UsersAdresses WHERE user_id = @user_id";
                #endregion
        #endregion

        #region SECRETQUESTION QUERIES
            #region SELECT
            public const string SELECTALLQUESTIONS = "SELECT * FROM SecretQuestions";
            public const string SELECTQUESTIONBYID = "SELECT * FROM SecretQuestions WHERE question_id = @id";
            #endregion

            #region INSERT
            public const string INSERTQUESTION = @"INSERT 
                    INTO SecretQuestions
                    (question_label)
                    VALUES
                    (@label)";
            #endregion

            #region UPDATE
            public const string UPDATEQUESTION = @"UPDATE 
                        SecretQuestions
                        SET question_label=@label
                        WHERE question_id=@id";
            #endregion

            #region DELETE
            public const string DELETEQUESTION = @"DELETE 
                        FROM SecretQuestions
                        WHERE question_id=@id";
            #endregion
        #endregion

        #region ROLE QUERIES
            #region SELECT
            public const string SELECTALLROLES = "SELECT * FROM Roles";
            public const string SELECTROLEBYID = "SELECT * FROM Roles WHERE role_id = @id";
            public const string SELECTROLEBYNAME = "SELECT * FROM Roles WHERE role_label = @label";
            #endregion

            #region INSERT
            public const string INSERTROLE = @"INSERT 
                    INTO Roles
                    (role_label)
                    VALUES
                    (@label)";
            #endregion

            #region UPDATE
            public const string UPDATEROLE = @"UPDATE 
                        Roles
                        SET role_label=@label
                        WHERE role_id=@id";
            #endregion

            #region DELETE
            public const string DELETEROLE = @"DELETE FROM Roles WHERE role_id=@id";
            public const string DELETEROLEBYNAME = @"DELETE FROM Roles WHERE role_label=@label";
            #endregion
        #endregion

        public const string SELECTIDENTITYAFTERINSERTSQLSERVER = "SELECT @@IDENTITY";
    }
}
